package com.scau.hwadee.examination.utils;

import com.scau.hwadee.examination.entity.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

/**
 * @author YHX
 * @description 导入Excel表
 * @date 2019/7/30 16:52
 **/
public class ImportExcelUtils {

  /**
   * @return java.lang.String
   * @author YHX
   * @description 统一把Excel单元格的内容转为字符串
   * @date 16:53 2019/7/30
   * @Param [cell]
   **/
  private static String changeCellType2String(HSSFCell cell) {
    if (cell == null) {
      return "";
    } else if (cell.getCellTypeEnum() == CellType.STRING) {
      return cell.getStringCellValue();
    } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
      return String.valueOf(cell.getNumericCellValue());
    } else {
      return "";
    }

  }

  /**
   * @return java.lang.String
   * @author YHX
   * @description 把读出来的小数点去掉
   * @date 16:53 2019/7/30
   * @Param [target]
   **/
  private static String withoutPoint(String target) {
    StringBuffer sb = new StringBuffer();
    for (char ch : target.toCharArray()) {
      if (ch == '.') {
        break;
      } else {
        sb.append(ch);
      }
    }
    return sb.toString();
  }

  /**
   * @return java.util.List<Nationwidecourse>
   * @author YHX
   * @description 导入全国课程
   * @date 17:10 2019/7/31
   * @Param [fis]
   **/
  public static List<Nationwidecourse> parseNationalCourseExcel(InputStream fis) throws Exception {
    List<Nationwidecourse> courses = new ArrayList<>();
    try {
      HSSFWorkbook workbook =
        new HSSFWorkbook(new POIFSFileSystem(fis));
      int numberOfSheets = workbook.getNumberOfSheets();
      for (int i = 0; i < numberOfSheets; i++) {
        HSSFSheet sheet = workbook.getSheetAt(i);
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        Nationwidecourse course;
        for (int j = 0; j < physicalNumberOfRows; j++) {
          if (j == 0) {
            continue;//标题行
          }
          HSSFRow row = sheet.getRow(j);
          if (row == null) {
            continue;//没数据
          }
          int physicalNumberOfCells = row.getPhysicalNumberOfCells();
          course = new Nationwidecourse();
          for (int k = 0; k < physicalNumberOfCells; k++) {
            HSSFCell cell = row.getCell(k);
            String cellValue = changeCellType2String(cell);
            switch (k) {
              case 0:
                course.setNcId(cellValue);
                break;
              case 1:
                course.setNcName(cellValue);
                break;
              case 2:
                course.setNcCredit(new BigDecimal(cellValue));
                break;
              default:
                break;
            }
          }
          courses.add(course);
        }
      }
      return courses;
    } catch (IOException e) {
      e.printStackTrace();
      return null;
    }
  }

  /**
   * @return java.util.List<com.scau.hwadee.examination.entity.Course>
   * @author YHX
   * @description 导入课程
   * @date 17:10 2019/7/31
   * @Param [fis]
   **/
  public static List<Course> parseCourseExcel(InputStream fis) throws Exception {
    List<Course> courses = new ArrayList<>();
    try {
      HSSFWorkbook workbook =
        new HSSFWorkbook(new POIFSFileSystem(fis));
      int numberOfSheets = workbook.getNumberOfSheets();
      for (int i = 0; i < numberOfSheets; i++) {
        HSSFSheet sheet = workbook.getSheetAt(i);
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        Course course;
        for (int j = 0; j < physicalNumberOfRows; j++) {
          if (j == 0) {
            continue;//标题行
          }
          HSSFRow row = sheet.getRow(j);
          if (row == null) {
            continue;//没数据
          }
          int physicalNumberOfCells = row.getPhysicalNumberOfCells();
          course = new Course();
          for (int k = 0; k < physicalNumberOfCells; k++) {
            HSSFCell cell = row.getCell(k);
            String cellValue = changeCellType2String(cell);
            if (k == 8 || k == 9 || k == 11 || k == 12 || k == 13 || k == 15) {
              cellValue = withoutPoint(cellValue);
            }
            switch (k) {
              case 0:
                course.setCsId(cellValue);
                break;
              case 1:
                course.setMvId(cellValue);
                break;
              case 2:
                course.setNcId(cellValue);
                break;
              case 3:
                course.setTbIsbn(cellValue);
                break;
              case 4:
                course.setCsName(cellValue);
                break;
              case 5:
                course.setCsIntro(cellValue);
                break;
              case 6:
                course.setCsTqsource(cellValue);
                break;
              case 7:
                course.setCsState(cellValue);
                break;
              case 8:
                course.setCsCredit(Integer.parseInt(cellValue));
                break;
              case 9:
                course.setCsPassline(Integer.parseInt(cellValue));
                break;
              case 10:
                course.setCsPointsys(cellValue);
                break;
              case 11:
                course.setCsSqmark(Integer.parseInt(cellValue));
                break;
              case 12:
                course.setCsOqmark(Integer.parseInt(cellValue));
                break;
              case 13:
                course.setCsTotalmark(Integer.parseInt(cellValue));
                break;
              case 14:
                course.setCsExamtime(cell.getDateCellValue());
                break;
              case 15:
                course.setCsIfpa(Byte.parseByte(cellValue));
                break;
              case 16:
                course.setCsProperties(cellValue);
                break;
              case 17:
                course.setCsRemark(cellValue);
                break;
              default:
                break;
            }
          }
          courses.add(course);
        }
      }
      return courses;
    } catch (IOException e) {
      e.printStackTrace();
      return null;
    }
  }


  /**
   * @return java.util.List<com.scau.hwadee.examination.entity.Major>
   * @author YHX
   * @description 导入全国专业
   * @date 17:11 2019/7/31
   * @Param [fis]
   **/
  public static List<Nationwidemajor> parseNationalMajorExcel(InputStream fis) throws Exception {
    List<Nationwidemajor> majors = new ArrayList<>();
    try {
      HSSFWorkbook workbook =
        new HSSFWorkbook(new POIFSFileSystem(fis));
      int numberOfSheets = workbook.getNumberOfSheets();
      for (int i = 0; i < numberOfSheets; i++) {
        HSSFSheet sheet = workbook.getSheetAt(i);
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        Nationwidemajor major;
        for (int j = 0; j < physicalNumberOfRows; j++) {
          if (j == 0) {
            continue;//标题行
          }
          HSSFRow row = sheet.getRow(j);
          if (row == null) {
            continue;//没数据
          }
          int physicalNumberOfCells = row.getPhysicalNumberOfCells();
          major = new Nationwidemajor();
          for (int k = 0; k < physicalNumberOfCells; k++) {
            HSSFCell cell = row.getCell(k);
            String cellValue = changeCellType2String(cell);
            if (k == 3) cellValue = withoutPoint(cellValue);
            switch (k) {
              case 0:
                major.setNmId(cellValue);
                break;
              case 1:
                major.setNmName(cellValue);
                break;
              case 2:
                major.setNmLevel(cellValue);
                break;
              case 3:
                major.setNmCredit(Integer.parseInt(cellValue));
                break;
              default:
                break;
            }
          }
          majors.add(major);
        }
      }
      return majors;
    } catch (IOException e) {
      e.printStackTrace();
      return null;
    }
  }

  public static List<Major> parseMajorExcel(InputStream fis) throws Exception {
    List<Major> majors = new ArrayList<>();
    try {
      HSSFWorkbook workbook =
        new HSSFWorkbook(new POIFSFileSystem(fis));
      int numberOfSheets = workbook.getNumberOfSheets();
      for (int i = 0; i < numberOfSheets; i++) {
        HSSFSheet sheet = workbook.getSheetAt(i);
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        Major major;
        for (int j = 0; j < physicalNumberOfRows; j++) {
          if (j == 0) {
            continue;//标题行
          }
          HSSFRow row = sheet.getRow(j);
          if (row == null) {
            continue;//没数据
          }
          int physicalNumberOfCells = row.getPhysicalNumberOfCells();
          major = new Major();
          for (int k = 0; k < physicalNumberOfCells; k++) {
            HSSFCell cell = row.getCell(k);
            String cellValue = changeCellType2String(cell);
            if (k == 4) System.out.println(cellValue);
            if (k == 19 || k == 20 || k == 21 || k == 16 || k == 22 || k == 3 || k == 1 || k == 2 || k == 4) {
              cellValue = withoutPoint(cellValue);
            }
            switch (k) {
              case 0:
                major.setMjId(cellValue);
                break;
              case 1:
                major.setMvId(cellValue);
                break;
              case 2:
                major.setMcId(cellValue);
                break;
              case 3:
                major.setNmId(cellValue);
                break;
              case 4:
                major.setMjFirstexaminer(cellValue);
                break;
              case 5:
                major.setMjName(cellValue);
                break;
              case 6:
                major.setMjIntro(cellValue);
                break;
              case 7:
                major.setMjType(cellValue);
                break;
              case 8:
                major.setMjLevel(cellValue);
                break;
              case 9:
                major.setMjExamform(cellValue);
                break;
              case 10:
                major.setMjFirstes(cellValue);
                break;
              case 11:
                major.setMjApprnum(cellValue);
                break;
              case 12:
                major.setMjStopnbes(cellValue);
                break;
              case 13:
                major.setMjStopes(cellValue);
                break;
              case 14:
                major.setMjStopreges(cellValue);
                break;
              case 15:
                major.setMjStopregnum(cellValue);
                break;
              case 16:
                major.setMjStopawdip(new Date(Integer.valueOf(cellValue)));
                break;
              case 17:
                major.setMjStopawnum(cellValue);
                break;
              case 18:
                major.setMjState(cellValue);
                break;
              case 19:
                major.setMjTotalcredit(new BigDecimal(cellValue));
                break;
              case 20:
                major.setMjGradcredit(new BigDecimal(cellValue));
                break;
              case 21:
                major.setMjTotalcourse(new BigDecimal(cellValue));
                break;
              case 22:
                major.setMjIfdiv(Byte.parseByte(cellValue));
                break;
              case 23:
                major.setMjRegexplain(cellValue);
                break;
              case 24:
                major.setMjGradexplain(cellValue);
                break;
              case 25:
                major.setMjRemark(cellValue);
                break;
              default:
                break;
            }
          }
          majors.add(major);
        }
      }
      return majors;
    } catch (IOException e) {
      e.printStackTrace();
      return null;
    }
  }


  /**
   * @return java.util.List<Unifiedexamschedule>
   * @author YSY
   * @description 导入统考课表
   * @date 10:58 2019/8/2
   * @Param [fis]
   **/
  public static List<Unifiedexamschedule> parseExaminationExcel(InputStream fis) throws Exception {
    List<Unifiedexamschedule> unifiedexamschedules = new ArrayList<>();
    try {
      HSSFWorkbook workbook =
        new HSSFWorkbook(new POIFSFileSystem(fis));
      int numberOfSheets = workbook.getNumberOfSheets();
      for (int i = 0; i < numberOfSheets; i++) {
        HSSFSheet sheet = workbook.getSheetAt(i);
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        Unifiedexamschedule unifiedexamschedule;
        for (int j = 0; j < physicalNumberOfRows; j++) {
          if (j == 0) {
            continue;//标题行
          }
          HSSFRow row = sheet.getRow(j);
          if (row == null) {
            continue;//没数据
          }
          int physicalNumberOfCells = row.getPhysicalNumberOfCells();
          unifiedexamschedule = new Unifiedexamschedule();
          for (int k = 0; k < physicalNumberOfCells; k++) {
            HSSFCell cell = row.getCell(k);
            String cellValue = changeCellType2String(cell);
            switch (k) {
              case 0:
                unifiedexamschedule.setCsId(cellValue);
                break;
              case 1:
                unifiedexamschedule.setEsId(cellValue);
                break;
              case 2:
                unifiedexamschedule.setEdTimecode(cellValue);
                break;
              default:
                break;
            }
          }
          unifiedexamschedules.add(unifiedexamschedule);
        }
      }
      return unifiedexamschedules;
    } catch (IOException e) {
      e.printStackTrace();
      return null;
    }
  }
}
